package cc.iotkit.screen.dao;

import cc.iotkit.common.satoken.utils.LoginHelper;
import cc.iotkit.common.utils.StringUtils;
import cc.iotkit.model.interceptors.DataScopeSqlBuilder;
import cc.iotkit.screen.dto.Period;
import cc.iotkit.screen.dto.QueryParamsVo;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Component;
import org.springframework.web.context.request.RequestContextHolder;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.util.List;
import java.util.Map;
import java.util.Set;

/***
 * 工作排名
 */
@Component
public class DeviceStatisticsRepositoryImpl implements DeviceStatisticsRepository{
    @PersistenceContext
    private EntityManager entityManager;

    /***
     * 获取设备清扫面积
     * @param deviceId
     * @param period
     * @param beginDate
     * @param endDate
     * @return
     */
    public List<Map<String,Object>> getSquareByDevice(String deviceId, String period,String beginDate, String endDate){
       String sql="SELECT %s ,round(sum(square),0) total,min(device_daily_report.device_id)device_id,min(robot_sn)robot_sn FROM device_daily_report\n" +
               "left join device_info on device_info.id=device_daily_report.device_id \n" +
               "where device_daily_report.device_id='%s' %s \n" +
               "GROUP BY %s";
        if(period.equals(Period.Type.month.toString())){
            sql=String.format(sql,"DATE_FORMAT(report_date,'%Y-%m') report_date",deviceId,"and DATE_FORMAT(report_date,'%Y-%m') BETWEEN '"+beginDate+"' and '"+endDate+"'","DATE_FORMAT(report_date,'%Y-%m')");
        }else if(period.equals(Period.Type.day.toString())){
            sql=String.format(sql,"report_date",deviceId,"and report_date BETWEEN '"+beginDate+"' and '"+endDate+"'","report_date");
        }else if(period.equals(Period.Type.week.toString())){
            sql=String.format(sql,"report_date", deviceId,"and report_date BETWEEN '"+beginDate+"' and '"+endDate+"'","report_date");
        }else if(period.equals(Period.Type.year.toString())){
            sql=String.format(sql,"DATE_FORMAT(report_date,'%Y') report_date",deviceId,"and DATE_FORMAT(report_date,'%Y') BETWEEN '"+beginDate+"' and '"+endDate+"'","DATE_FORMAT(report_date,'%Y')");
        }else if(period.equals(Period.Type.hour.toString())){
            sql="SELECT a.id,a.report_date,a.`hour`,a.device_id,a.online_times,a.distance," +
                    "round(a.square,0) square,a.dts,device_info.robot_sn FROM `device_hourly_report` a\n" +
                    "left join device_info on device_info.id=a.device_id\n" +
                    "where report_date='"+beginDate+"' and a.device_id='"+deviceId+"'";
        }
        List<Map<String,Object>> result= entityManager.createNativeQuery(sql).unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .getResultList();
        return result;
    }

    /***
     * 获取所有设备清扫面积
     * @return
     */
    public List<Map<String,Object>> getCleanSquare(QueryParamsVo queryParams){
        String sql="SELECT *,round(t.total_area/target_area*100,0)area_completion_rate,round(total_online_hours/target_hours*100,0)online_area_completion_rate from(" +
                "SELECT round(sum(square),0)total_area,round(sum(working_times)/3600,0)total_online_hours,count(distinct b.device_id) device_count %s  " +
                "from device_daily_report a\n" +
                "inner join device_info b on a.device_id=b.device_id\n" +
                "left join product c on c.product_key=b.product_key\n" +
                "left join product_model d on b.model_id=d.id\n"+
                "left join sys_dept e on b.create_dept=e.id\n"+
                "where  %s " +
                "GROUP BY %s)t";
        String selectSql="";
        String whereSql="";
        String groupSql="";
        if(StringUtils.isNotEmpty(queryParams.getCreateDept())){
            selectSql+=",b.create_dept,min(e.`dept_name`) dept_name";
            whereSql+=" and (b.create_dept="+queryParams.getCreateDept()+" or  e.ancestors like '"+queryParams.getAncestors()+","+queryParams.getCreateDept()+"%')";
            groupSql+=",b.create_dept";
        }
        if(StringUtils.isNotEmpty(queryParams.getProductKey())){
            selectSql+=",b.product_key,min(c.`name`) product_name";
            whereSql+=" and b.product_key='"+queryParams.getProductKey()+"'";
            groupSql+=",b.product_key";
        }
        if(StringUtils.isNotEmpty(queryParams.getModelId())){
            selectSql+=",b.model_id,min(d.model) model";
            whereSql+=" and b.model_id='"+queryParams.getModelId()+"'";
            groupSql+=",b.model_id";
        }
        if(StringUtils.isNotEmpty(queryParams.getDeviceId())){
            selectSql+=",a.device_id,min(b.robot_sn) robot_sn";
            whereSql+=" and a.device_id='"+queryParams.getDeviceId()+"'";
            groupSql+=",a.device_id";
        }

        if(queryParams.getPeriod().name().equals(Period.Type.month.toString())){
            selectSql+=",DATE_FORMAT(report_date,'%Y-%m') report_date," +
                    "round(sum(b.target_area)) target_area," +
                    "round(sum(b.target_hours)) target_hours";
            whereSql="DATE_FORMAT(report_date,'%Y-%m') BETWEEN '"+queryParams.getStartDate()+"' and '"+queryParams.getEndDate()+"' "+whereSql;
            groupSql="DATE_FORMAT(report_date,'%Y-%m')"+groupSql;
        }else if(queryParams.getPeriod().name().equals(Period.Type.day.toString())){
            selectSql+=",report_date," +
                    "round(sum( b.target_area ),0) target_area,"+
                    "round(sum( b.target_hours ),0) target_hours";
            whereSql="report_date BETWEEN '"+queryParams.getStartDate()+"' and '"+queryParams.getEndDate()+"' "+whereSql;
            groupSql="report_date"+groupSql;
        }else if(queryParams.getPeriod().name().equals(Period.Type.year.toString())){
            selectSql+=",DATE_FORMAT(report_date,'%Y') report_date,sum(b.target_area) target_area"+
                    ",sum(b.target_hours) target_hours";
            whereSql="DATE_FORMAT(report_date,'%Y') BETWEEN '"+queryParams.getStartDate()+"' and '"+queryParams.getEndDate()+"' "+whereSql;
            groupSql="DATE_FORMAT(report_date,'%Y')"+groupSql;
        }

        //拼接部门权限
        String deptFilter= DataScopeSqlBuilder.build("b");
        if(StringUtils.isNotEmpty(deptFilter)){
            whereSql+=deptFilter;
        }

        sql=String.format(sql,selectSql,whereSql,groupSql);

        List<Map<String,Object>> result= entityManager.createNativeQuery(sql).unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .getResultList();
        return result;
    }

    /**
     * 按月统计部门清扫、洗地数据
     * 可按三级部门统计，第一级为租户汇总，第二、三级为设备汇总
     * @return
     */
    public List<Map<String,Object>> getMonthlyDeptDeviceCleanData(QueryParamsVo queryParams){
        //层级数，从0开始
        int level= queryParams.getAncestors().split(",").length-1;
        int subindex=level>1?2:3;
        String sql="SELECT round(sum(square),0)total_area ,round(sum(online_times)/3600,0)total_online_hours,\n" +
                "b.product_key,min(c.`name`) product_name,b.model_id,min(d.model) model, a.device_id,min(b.robot_sn) robot_sn,\n" +
                "min(b.create_dept)create_dept,min(e.dept_name)dept_name,concat(min(f.ancestors),',',min(f.id))ancestors,min(f.id)parent_dept_id,min(f.dept_name)parent_dept_name,min(f.ancestors)parent_ancestors,\n" +
                "min(b.target_area*DATEDIFF(LAST_DAY('%s-1'),'%s-01')) target_area,\n" +
                "min(b.target_hours*DATEDIFF(LAST_DAY('%s-1'),'%s-01')) target_hours,\n" +
                "count(distinct b.device_id) device_count\n" +
                "from device_daily_report a\n" +
                "inner join device_info b on a.device_id=b.device_id\n" +
                "left join product c on c.product_key=b.product_key\n" +
                "left join product_model d on d.id=b.model_id\n" +
                "inner join sys_dept e on b.create_dept=e.id\n" +
                //#判断ancestors中逗号个数，如>0,按照层级对应的部门id关联部门，例：0,100 关联部门id=100
                "left join sys_dept f on  (case when LENGTH(e.ancestors) - LENGTH(REPLACE(e.ancestors, ',', ''))>%s then SUBSTRING_INDEX(SUBSTRING_INDEX(e.ancestors, ',', %s), ',', -1) else e.id end)=f.id \n" +
                "where DATE_FORMAT(report_date,'%%Y-%%m') BETWEEN '%s' and '%s' and (e.ancestors like '%s%%' %s) %s\n" +
                "group by b.product_key,b.model_id,a.device_id";

        String deptstr= "";
        if(level>0){
            deptstr=" or b.create_dept="+queryParams.getCreateDept();
        }
        //拼接部门权限
        String deptFilter= DataScopeSqlBuilder.build("b");

        sql=String.format(sql,queryParams.getEndDate(),
                queryParams.getStartDate(),queryParams.getEndDate(),queryParams.getStartDate(),
                level,subindex,queryParams.getStartDate(),queryParams.getEndDate(),queryParams.getAncestors(),deptstr,deptFilter);

        //判断是否为最上级,如果是则按部门汇总
        if(level==0){
            String sql2="SELECT parent_dept_name,min(ancestors)ancestors,min(parent_dept_id)parent_dept_id," +
                    "round(sum(total_area),0) total_area,round(sum(total_online_hours),0) total_online_hours," +
                    "round(sum(target_area),0)target_area," +
                    "round(sum(target_hours),0) target_hours,round(sum(device_count),0) device_count " +
                    "from(%s)t " +
                    "GROUP BY parent_dept_name";
            sql=String.format(sql2,sql);
        }

        List<Map<String,Object>> result= entityManager.createNativeQuery(sql).unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .getResultList();
        return result;
    }
}
